# import pdfplumber
# import pandas as pd
# import numpy as np
# from _sqlite3 import connect
# import openpyxl
# from openpyxl.reader.excel import load_workbook
# global tables
# import pyodbc
# import re
# import os
# class PDFTableExtractor:
#     def __init__(self, pdf_path):
#         self.pdf_path = pdf_path
#         self.report_name = os.path.basename(pdf_path).replace("：", ":")
#         self.JZRQ = os.path.basename(pdf_path)
#         self.JZRQ = re.findall(r'\d+', self.JZRQ)[0]
#
#
#     def find_keyword_and_extract_table(self, keyword,IGSDM):
#         self.IGSDM = IGSDM
#         with pdfplumber.open(self.pdf_path) as pdf:
#             for page_num in range(len(pdf.pages)):
#                 page = pdf.pages[page_num]
#                 text = page.extract_text()
#                 if page_num == 0:
#                     lines = text.split('\n')
#                     self.ZWMC = lines[2].strip()
#                     print(self.ZWMC)
#                     print(self.report_name)  ##
#                 ##字段获
#                 self.chixu = False
#                 if keyword in text:
#                     self.chixu = False
#                     print(f'在第 {page_num + 1} 页找到关键词 "{keyword}"。')
#                     tables = self.extract_table_from_page(page)
#                     excel_file = f'产能产量tables_page_{self.JZRQ}.xlsx'
#                     self.save_tables_to_excel(tables, excel_file)
#                     if self.chixu == True:
#                             page2 = pdf.pages[page_num + 1]
#                             print(excel_file)
#                             tables2 = self.concat_table2(page2)
#                             if self.chixu2 == True:
#                                 a = max([len(sub_list) for sub_list in tables if isinstance(sub_list, list)])
#                                 b = max([len(sub_list) for sub_list in tables2 if isinstance(sub_list, list)])
#
#                                 print(f'上一页表格最大列数 {a}，下一页表格最大列数 {b}')
#                                 if a == b:
#                                     print('下一页的表格在页首，拼接表格')
#                                     print(tables2[0][0])
#                                     if (tables2[0][0] == '' and tables2[0][1] == '' and tables2[0][2] == '' ):
#                                        print('极大可能指标！单元格跨页情况')
#                                        tables2 = tables2[1:]
#                                        if len(tables2)==7:
#                                         tables[-1][1:] = ['生产量', '销售量', '库存量', '生产量比上年增减','销售量比上年增减', '库存量比上年增减']
#                                        elif len(tables2)==8:
#                                            tables[-1][1:] = ['单位','生产量', '销售量', '库存量', '生产量比上年增减',
#                                                              '销售量比上年增减', '库存量比上年增减']
#                                        tables.extend(tables2)
#                             self.save_tables_to_excel(tables, excel_file)
#                     return tables
#
#
#     def extract_table_from_page(self, page):
#         tables = page.extract_tables()
#         for i, table in enumerate(tables):
#             df = pd.DataFrame(table).replace('\n', '', regex=True)
#             if df[0][0] == '主要产品':
#                 tables=table
#                 break
#             else:tables=None
#         if tables != None:##还没有变一维的表格判断
#             text=page.extract_text()
#             lines=text.split('\n')
#             last_line=lines[-2].strip()##本页倒数的文字
#             last_table=tables[-1]##本表格倒数的文字
#             last_table = ' '.join(str(item) for item in last_table if item is not None)
#             Frist_line=lines[1].strip() ##本页第一页文字
#             Frist_table=tables[0][0]##本表格第一行文字
#             Frist_table=' '.join(str(item) for item in Frist_table if item is not None)
#             self.chixu = last_line==last_table
#             self.first=Frist_line==Frist_table
#             for wenzi in last_line:
#                 if wenzi in last_table:
#                     self.chixu = True
#                     break
#                 else:
#                     self.chixu = False
#             print(self.chixu)
#         return tables
#
#     def concat_table2(self, page):
#         tables2 = page.extract_tables()
#         text = page.extract_text()
#         lines = text.split('\n')
#         Frist_line = lines[1].strip()
#         Frist_table = tables2[0][0]
#         Frist_table = ' '.join(str(item) for item in Frist_table if item is not None)
#         self.chixu2 = Frist_line == Frist_table
#         print(Frist_line)
#         for wenzi in Frist_line:
#             print(wenzi)
#             if wenzi in Frist_table:
#                 self.chixu2 = True
#                 print('下页表格触顶')
#                 break
#             else:
#                 self.chixu2 = False
#         print(f'self.chixu2:{self.chixu2}')
#         excel_file = f'tables2_page_{self.JZRQ}.xlsx'
#         for i, x in enumerate(tables2):
#             if i == 0:
#                 a = x
#                 self.save_tables_to_excel(a, excel_file=excel_file)
#                 return a
#
#     def save_tables_to_excel(self, tables, excel_file):
#         with pd.ExcelWriter(excel_file, engine='xlsxwriter') as writer:
#                 df = pd.DataFrame(tables).replace('\n', '', regex=True)
#                 df = df.replace(' ', '')
#                 print(df.shape)
#                 # Check if the DataFrame has 8 columns
#                 # if df.shape[1] == 8:
#                 #     # Delete the second column (index 1)
#                 #     df = df.drop(df.columns[1], axis=1)
#                 sheet_name = f'产销表'
#                 df.to_excel(writer, sheet_name=sheet_name, index=False)
#                 print(f'Table 产销表 saved to {excel_file} ')
#     def get_code(self):
#         db_name = r'C:\Users\yuankf47185\PycharmProjects\客户端尝试\mydatabase.db'
#         con = connect(db_name)
#         Product = "SELECT * FROM product_code"
#         self.PRODUCT_CODE = pd.read_sql(Product, con)
#         ZB = 'SELECT * FROM ZB_CODE'
#         self.ZB_CODE = pd.read_sql(ZB, con)
#         DQ = 'SELECT * FROM city_code'
#         self.DQ_CODE = pd.read_sql(DQ, con)
#         BT='SELECT * FROM GG  '
#         self.BT=pd.read_sql(BT,con)
#         con.close()
#
#     def xggs(self):
#         excel_file_path = f'产能产量tables_page_{self.JZRQ}.xlsx'
#         new_excel_file_path = f'产能产量{self.JZRQ}企业经营.xlsx'
#         workbook = openpyxl.load_workbook(excel_file_path)
#         sheet_names = workbook.sheetnames
#         with pd.ExcelWriter(new_excel_file_path, engine='openpyxl') as writer:
#             for i, sheet_name in enumerate(sheet_names, start=1):
#                 print('Processing sheet产能产量')
#                 yysr = pd.read_excel(excel_file_path, sheet_name=sheet_name).replace(' ','',regex=True)
#                 new_columns= yysr.iloc[0].replace('主要产品', 'SJLMYMC')
#                 yysr = yysr.rename(columns=new_columns)
#                 yysr = yysr.drop(0)
#                 if '单位' in yysr.columns:
#                     yysr=yysr.drop('单位',axis=1)
#                 yysr = pd.melt(yysr,
#                                id_vars='SJLMYMC',
#                                value_vars=list(yysr.columns)[1:],
#                                var_name='ZBMC',
#                                value_name='ZBSJ')
#                 yysr['EPBH']=None
#                 yysr['XXLY'] = None
#                 yysr['JZRQ'] = None
#                 yysr['ZTYSMC'] = None
#                 yysr['SJLMY'] = None
#                 yysr['ZBDW'] = None
#                 yysr['SJLME'] = None
#                 yysr['SJLMS'] = None
#                 yysr['TJKJ'] = None
#                 yysr['JZRQ']=None
#                 yysr['TJQJ']=None
#                 yysr['JZRQ'].iloc[:len(yysr)] = self.JZRQ + '-12-31'
#                 yysr['SJLMY'].iloc[:len(yysr)]='产品'
#                 yysr['EPBH'].iloc[:len(yysr)] = str(self.EPBH)
#                 yysr['XXLY'].iloc[:len(yysr)] = str(self.report_name).replace('.pdf', '')
#                 yysr['TJQJ'].iloc[:len(yysr)]='年'
#                 yysr['XXFBRQ']= yysr.merge(self.BT, on='XXLY', how='left')['XXFBRQ']
#                 KJ = [
#                     yysr['ZBMC']==('生产量'),
#                     yysr['ZBMC']==('销售量'),
#                     yysr['ZBMC']==('库存量'),
#                     yysr['ZBMC'].str.contains('生产量比上年增减'),
#                     yysr['ZBMC'].str.contains('销售量比上年增减'),
#                     yysr['ZBMC'].str.contains('库存量比上年增减')
#                 ]
#                 KJ_VALUES=['当期值','当期值','期末值','当期同比增长率','当期同比增长率','期末同比增长率']
#                 yysr['TJKJ'] = np.select(KJ, KJ_VALUES)
#                 DW_VALUES =['','','','%','%','%']
#                 yysr['ZBDW']=np.select(KJ,DW_VALUES)
#                 JYYWLXDM_VALUES =['产能产量','销售情况（按产品类型）','销售情况（按产品类型）','产能产量','销售情况（按产品类型）','销售情况（按产品类型）']
#                 yysr['JYYWLXDM']=np.select(KJ,JYYWLXDM_VALUES)
#                 yysr['SJLMYDM'] = yysr.merge(self.PRODUCT_CODE, on='SJLMYMC', how='left')['标准代码']
#                 yysr['ZBMC']=yysr['ZBMC'].replace('比','',regex=True).replace('上','',regex=True).replace('年','',regex=True).replace('增减','',regex=True).replace('（%）','',regex=True)
#                 yysr['ZBMC'] =yysr['ZBMC'].replace('生产量','产量').replace('销售量','销量')
#                 yysr['ZBDM'] = yysr.merge(self.ZB_CODE, on='ZBMC', how='left')['ZBDM']
#                 yysr = yysr.reindex(
#                     columns=['EPBH', 'XXLY', 'XXFBRQ', 'JZRQ', 'JYYWLXDM', 'SJLMY', 'SJLMYMC', 'SJLMYDM',
#                              'SJLME', 'SJLMEMC', 'SJLMEDM', 'SJLMS', 'SJLMSMC', 'ZTYSMC', 'ZBDM', 'ZBMC', 'ZBSJ',
#                              'ZBDW', 'TJKJ', 'TJQJ'])
#                 yysr.to_excel(writer, index=False, sheet_name=sheet_name)
#                 return new_excel_file_path
#     def get_EPBH(self):
#         server = 'SHPUBSRV02'
#         database = 'JYCFI'
#         conn = pyodbc.connect(
#             'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
#         query = """
#                 SELECT DISTINCT EPBH
#                 FROM [10.106.22.51].JYPRIME.dbo.usrQYMB GG
#                 WHERE QYBH=?
#                     """
#         IGSDM = self.IGSDM
#         self.EPBH = pd.read_sql(query, conn, params=[IGSDM])
#         try:
#             self.EPBH = self.EPBH['EPBH'][0]
#         except:
#             self.EPBH = 'EP未匹配成功，需要人工处理'
#         return self.EPBH
# def run_all(input_folder, IGSDM):
#         all_merged_data = pd.DataFrame()
#         for pdf_file in os.listdir(input_folder):
#             if pdf_file.endswith('.pdf'):
#                 pdf_path = os.path.join(input_folder, pdf_file)
#                 # Process each PDF file
#                 pdf_table_extractor = PDFTableExtractor(pdf_path)
#                 pdf_table_extractor.get_code()
#                 keyword_to_find = '产销量情况分析表'
#                 tables = pdf_table_extractor.find_keyword_and_extract_table(keyword_to_find, IGSDM)
#                 pdf_table_extractor.get_EPBH()
#                 print(f'tables: {tables}')
#                 if tables !=None :
#                     try:
#                         result_merged=pdf_table_extractor.xggs()
#                         current_merged_data = pd.read_excel(result_merged)
#                         all_merged_data = pd.concat([all_merged_data, current_merged_data], ignore_index=True)
#                         EPBH = pdf_table_extractor.get_EPBH()
#                         output_excel_path_all = f'产能产量EPBH_{EPBH}.xlsx'
#                         all_merged_data.to_excel(output_excel_path_all, index=False, sheet_name='拼接_all')
#                     except:
#                         print(f'格式披露有问题')
#
#                         print(f'{pdf_file}年报,未找到关键字')
# if __name__ == '__main__':
#     input_folder = '../downloaded_files/631125'
#     IGSDM = '631125'
#     run_all(input_folder, IGSDM)
#
#
#
#